package DAO;

import java.sql.*;
import javax.swing.*;

/**
 * Contains the functions that contains the sql queries that can manipulate and access the database
 * @author Jason, Joshua and Sam
 */
public class MySQLFlightDAO implements FlightDAO {

    private ResultSet rs;
    private Statement stmt;

    /**
     * Preforms a very complex sql query and adds the results to the JPanel
     * @return JPanel
     */
    public JPanel getFlightsInfo() {
        MySQLDAOFactory a = new MySQLDAOFactory();
        String departure = null, destination = null, dep_time = null, arr_time = null;
        JPanel flightInfoPanel = new JPanel();
        flightInfoPanel.setLayout(new BoxLayout(flightInfoPanel, BoxLayout.Y_AXIS));
        Connection con = a.createConnection();
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT DEP.Departs AS Departs, DEP.Time AS DepartTime, DEP.Date AS DepartDate, ARR.Arrives AS Arrives, ARR.Time AS ArriveTime, ARR.Date AS ArriveDate, FLIGHT_LEG.SEATS_AVAILABLE AS Seats, TICKET.PRICE AS Price " +
                    "FROM " +
                    "(SELECT AP.AIRPORT_LOCATION AS Departs, D.TIME AS Time, D.DATE AS Date, FL.LEG_ID AS Leg " +
                    "FROM FLIGHT_LEG AS FL, DEPARTURE AS D, AIRPORT AS AP " +
                    "WHERE FL.DEPART_ID = D.DEPART_ID " +
                    "AND D.AIRPORT_ID = AP.AIRPORT_ID) AS DEP, " +
                    "(SELECT AP.AIRPORT_LOCATION AS Arrives, A.TIME AS Time, A.DATE AS Date, FL.LEG_ID AS Leg " +
                    "FROM FLIGHT_LEG AS FL, ARRIVAL AS A, AIRPORT AS AP " +
                    "WHERE FL.ARRIVE_ID = A.ARRIVE_ID " +
                    "AND A.AIRPORT_ID = AP.AIRPORT_ID) AS ARR, FLIGHT_LEG, FLIGHT, TICKET " +
                    "WHERE DEP.Leg = ARR.Leg " +
                    "AND ARR.Leg = FLIGHT_LEG.LEG_ID " +
                    "AND FLIGHT.FLIGHT_ID = FLIGHT_LEG.FLIGHT_ID " +
                    "AND TICKET.FLIGHT_ID = FLIGHT.FLIGHT_ID; ");
            while (rs.next()) {
                JPanel flightPanel = new JPanel();
                JPanel departurePanel = new JPanel();
                JLabel departureLabel = new JLabel("Departure: ");
                JLabel departureInfoLabel = new JLabel();

                JPanel departureDatePanel = new JPanel();
                JLabel departureDateLabel = new JLabel("Departure Date: ");
                JLabel departureDateInfoLabel = new JLabel();

                JPanel departureTimePanel = new JPanel();
                JLabel departureTimeLabel = new JLabel("Departure Time: ");
                JLabel departureTimeInfoLabel = new JLabel();

                JPanel destinationPanel = new JPanel();
                JLabel destinationLabel = new JLabel("Destination: ");
                JLabel destinationInfoLabel = new JLabel();

                JPanel arrivalDatePanel = new JPanel();
                JLabel arrivalDateLabel = new JLabel("Arrival Date: ");
                JLabel arrivalDateInfoLabel = new JLabel();

                JPanel arrivalTimePanel = new JPanel();
                JLabel arrivalTimeLabel = new JLabel("Arrival Time: ");
                JLabel arrivalTimeInfoLabel = new JLabel();

                JPanel seatsAvailablePanel = new JPanel();
                JLabel seatsAvailableLabel = new JLabel("Seats Available: ");
                JLabel seatsAvailableInfoLabel = new JLabel();

                JPanel pricePanel = new JPanel();
                JLabel priceLabel = new JLabel("Price: $");
                JLabel priceInfoLabel = new JLabel();

                JButton editFlightDetails = new JButton("Edit");

                departureInfoLabel.setText(rs.getString("Departs"));
                departureDateInfoLabel.setText(rs.getString("DepartDate"));
                departureTimeInfoLabel.setText(rs.getString("DepartTime"));
                destinationInfoLabel.setText(rs.getString("Arrives"));
                arrivalDateInfoLabel.setText(rs.getString("ArriveDate"));
                arrivalTimeInfoLabel.setText(rs.getString("ArriveTime"));
                seatsAvailableInfoLabel.setText(rs.getString("Seats"));
                priceInfoLabel.setText(rs.getString("Price"));

                flightPanel.add(departurePanel);
                departurePanel.add(departureLabel);
                departurePanel.add(departureInfoLabel);

                flightPanel.add(departureDatePanel);
                departureTimePanel.add(departureDateLabel);
                departureTimePanel.add(departureDateInfoLabel);

                flightPanel.add(departureTimePanel);
                departureTimePanel.add(departureTimeLabel);
                departureTimePanel.add(departureTimeInfoLabel);

                flightPanel.add(destinationPanel);
                destinationPanel.add(destinationLabel);
                destinationPanel.add(destinationInfoLabel);

                flightPanel.add(arrivalDatePanel);
                arrivalTimePanel.add(arrivalDateLabel);
                arrivalTimePanel.add(arrivalDateInfoLabel);

                flightPanel.add(arrivalTimePanel);
                arrivalTimePanel.add(arrivalTimeLabel);
                arrivalTimePanel.add(arrivalTimeInfoLabel);

                flightPanel.add(seatsAvailablePanel);
                seatsAvailablePanel.add(seatsAvailableLabel);
                seatsAvailablePanel.add(seatsAvailableInfoLabel);

                flightPanel.add(pricePanel);
                pricePanel.add(priceLabel);
                pricePanel.add(priceInfoLabel);

                flightPanel.add(editFlightDetails);
                flightPanel.setVisible(true);

                flightInfoPanel.add(flightPanel);
            }
            con.close();
        } catch (Exception e) {
            System.out.println("Error message " + e.getMessage());
        }
        flightInfoPanel.setVisible(true);
        return flightInfoPanel;
    }
}
